﻿using System;
using System.Collections.Generic;
using System.Text;
using RaisingStudio.Data.Common.Managers;

namespace RaisingStudio.Data.Common.Builders
{
    /// <summary>
    /// dataset Common Command builder.
    /// </summary>
    public class DataSetCommonCommandBuilder
    {
        #region static member
        /// <summary>
        /// Check an array of string contains the specified string or not.
        /// </summary>
        /// <param name="stringArray">An array of string.</param>
        /// <param name="value">The specified string.</param>
        /// <returns>If contains return true, otherwise return false.</returns>
        public static bool ContainsValue(string[] stringArray, string value)
        {
            return (System.Array.IndexOf(stringArray, value) >= 0);
        }

        /// <summary>
        /// Gets An array of Columns.
        /// </summary>
        /// <param name="columnNames">column name.</param>
        /// <returns>An array of Columns.</returns>
        public static string[] GetColumnNames(string columnNames)
        {
            return columnNames.Split(',');
        }

        /// <summary>
        /// Gets datatable Type.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="typeName">Type name.</param>
        /// <returns>datatable Type.</returns>
        public static System.Type GetDataTableType(System.Type type, string typeName)
        {
#if USE_TYPEMANAGER
            return RaisingStudio.Reflection.TypeManager.GetType(type, typeName);
#else
            System.Reflection.Assembly assembly;
            if (typeName.IndexOf(",") >= 0)
            {
                string[] names = typeName.Split(',');
                assembly = System.Reflection.Assembly.LoadFrom(names[0]);
                typeName = names[1];
            }
            else
            {
                assembly = type.Assembly;
            }

            return assembly.GetType(typeName);
#endif
        }

        /// <summary>
        /// Gets datatable Type.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="typeName">Type name.</param>
        /// <returns>datatable Type.</returns>
        public static System.Type GetDataTableType(string typeName)
        {
#if USE_TYPEMANAGER
            return RaisingStudio.Reflection.TypeManager.GetType(typeName);
#else
            return System.Type.GetType(typeName);
#endif
        }

        /// <summary>
        /// Generate table structure definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>table structure definition.</returns>
        public static DataTableDefinition GenerateDataTableDefinition(System.Xml.XmlElement xmlElement)
        {
            return CommonCommandBuilder.GenerateDataTableDefinition(null, xmlElement);
        }

        /// <summary>
        /// Generate table structure definition.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <returns>table structure definition.</returns>
        public static DataTableDefinition GenerateDataTableDefinition(System.Type type)
        {
            System.Xml.XmlElement xmlElement = DefinitionManager.Instance.LoadXmlDocument(type);
            return GenerateDataTableDefinition(xmlElement);
        }

        /// <summary>
        /// Generate table structure definition.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="name">Data object name.</param>
        /// <returns>table structure definition.</returns>
        public static DataTableDefinition GenerateDataTableDefinition(System.Type type, string name)
        {
            System.Xml.XmlElement xmlElement = DefinitionManager.Instance.LoadXmlDocument(type, name);
            return GenerateDataTableDefinition(xmlElement);
        }

        /// <summary>
        /// generate table structure definition.
        /// </summary>
        /// <param name="dataSetType">dataset Type.</param>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>dictionary of table structure definition.</returns>
        public static Dictionary<string, DataTableDefinition> GenerateDataTableDefinitions(System.Type dataSetType, System.Xml.XmlElement xmlElement)
        {
            Dictionary<string, DataTableDefinition> dataTableDefinitions = new Dictionary<string, DataTableDefinition>();
            // generate table structure definition.
            foreach (System.Xml.XmlNode tablesXmlNode in xmlElement)
            {
                if (tablesXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement tablesXmlElement = tablesXmlNode as System.Xml.XmlElement;
                    if (tablesXmlElement.Name == "Tables")
                    {
                        foreach (System.Xml.XmlNode tableXmlNode in tablesXmlElement)
                        {
                            if (tableXmlNode is System.Xml.XmlElement)
                            {
                                System.Xml.XmlElement tableXmlElement = tableXmlNode as System.Xml.XmlElement;
                                string tableName = tableXmlElement.Attributes["TableName"].Value.Trim();
                                if (tableXmlElement.Attributes["Type"] != null)
                                {
                                    string typeName = tableXmlElement.Attributes["Type"].Value.Trim();
                                    // Gets datatable Type.
                                    System.Type dataTableType = GetDataTableType(dataSetType, typeName);

                                    if (tableXmlElement.Attributes["DefinitionName"] != null)
                                    {
                                        string definitionName = tableXmlElement.Attributes["DefinitionName"].Value.Trim();
                                        // Generate table structure definition.
                                        DataTableDefinition dataTableDefinition = GenerateDataTableDefinition(dataTableType, definitionName);
                                        // Add table structure definition.
                                        dataTableDefinitions.Add(tableName, dataTableDefinition);
                                    }
                                    else
                                    {
                                        // Generate table structure definition.
                                        DataTableDefinition dataTableDefinition = GenerateDataTableDefinition(dataTableType);
                                        // Add table structure definition.
                                        dataTableDefinitions.Add(tableName, dataTableDefinition);
                                    }
                                }
                                else
                                {
                                    if (tableXmlElement.Attributes["DefinitionName"] != null)
                                    {
                                        string definitionName = tableXmlElement.Attributes["DefinitionName"].Value.Trim();
                                        // Generate table structure definition.
                                        DataTableDefinition dataTableDefinition = GenerateDataTableDefinition(dataSetType, definitionName);
                                        // Add table structure definition.
                                        dataTableDefinitions.Add(tableName, dataTableDefinition);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return dataTableDefinitions;
        }
        
        /// <summary>
        /// Create Common Command builder.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>Common Command builder.</returns>
        public static CommonCommandBuilder CreateCommonCommandBuilder(System.Xml.XmlElement xmlElement)
        {
            return new CommonCommandBuilder(xmlElement);
        }

        /// <summary>
        /// Create Common Command builder.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <returns>Common Command builder.</returns>
        public static CommonCommandBuilder CreateCommonCommandBuilder(System.Type type)
        {
            System.Xml.XmlElement xmlElement = DefinitionManager.Instance.LoadXmlDocument(type);
            return CreateCommonCommandBuilder(xmlElement);
        }

        /// <summary>
        /// Create Common Command builder.
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="name">Data object name.</param>
        /// <returns>Common Command builder.</returns>
        public static CommonCommandBuilder CreateCommonCommandBuilder(System.Type type, string name)
        {
            System.Xml.XmlElement xmlElement = DefinitionManager.Instance.LoadXmlDocument(type, name);
            return CreateCommonCommandBuilder(xmlElement);
        }

        /// <summary>
        /// generate table relateion definition.
        /// </summary>
        /// <param name="xmlElement">xml element.</param>
        /// <returns>table relateion definition.</returns>
        public static Dictionary<string, DataRelationDefinition> GenerateDataRelationDefinitions(System.Xml.XmlElement xmlElement)
        {
            Dictionary<string, DataRelationDefinition> dataRelationDefinitions = new Dictionary<string, DataRelationDefinition>();
            // generate table relateion definition.
            foreach (System.Xml.XmlNode relationsXmlNode in xmlElement)
            {
                if (relationsXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement relationsXmlElement = relationsXmlNode as System.Xml.XmlElement;
                    if (relationsXmlElement.Name == "Relations")
                    {
                        foreach (System.Xml.XmlNode relationXmlNode in relationsXmlElement)
                        {
                            if (relationXmlNode is System.Xml.XmlElement)
                            {
                                System.Xml.XmlElement relationXmlElement = relationXmlNode as System.Xml.XmlElement;
                                string relationName = relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.RelationName].Value;
                                string parentTableName = relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.ParentTable].Value;
                                string parentColumnNames = relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.ParentColumns].Value;
                                string childTableName = relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.ChildTable].Value;
                                string childColumnNames = relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.ChildColumns].Value;

                                string[] parentColumns = GetColumnNames(parentColumnNames);
                                string[] childColumns = GetColumnNames(childColumnNames);
                                // create Definition of relation.
                                DataRelationDefinition dataRelationDefinition = new DataRelationDefinition(relationName, parentTableName, parentColumns, childTableName, childColumns);
                                if (relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.CreateConstraints] != null)
                                {
                                    dataRelationDefinition.Constraint = bool.Parse(relationXmlElement.Attributes[RaisingStudio.Data.DataSet.DefinitionAttributeNames.CreateConstraints].Value);
                                }

                                // Add Definition of relation.
                                dataRelationDefinitions.Add(relationName, dataRelationDefinition);
                            }
                        }
                    }
                }
            }
            return dataRelationDefinitions;
        }

        /// <summary>
        /// copy table relateion definition.
        /// </summary>
        /// <param name="dataRelationDefinitions">table relateion definition.</param>
        /// <param name="constraint">copy the constraint relation.</param>
        /// <returns>a copy of relation dictionary.</returns>
        public static Dictionary<string, DataRelationDefinition> CopyRelationDefinitions(Dictionary<string, DataRelationDefinition> dataRelationDefinitions, bool constraint)
        {
            Dictionary<string, DataRelationDefinition> dataRelationDefinitionsCopy = new Dictionary<string, DataRelationDefinition>();
            foreach (string relationName in dataRelationDefinitions.Keys)
            {
                if (constraint)
                {
                    DataRelationDefinition dataRelationDefinition = dataRelationDefinitions[relationName];
                    if (dataRelationDefinition.Constraint)
                    {
                        dataRelationDefinitionsCopy.Add(relationName, dataRelationDefinition);
                    }
                }
                else
                {
                    dataRelationDefinitionsCopy.Add(relationName, dataRelationDefinitions[relationName]);
                }
            }
            return dataRelationDefinitionsCopy;
        }

        /// <summary>
        /// sort the table according the relation.
        /// </summary>
        /// <param name="dataRelationDefinitions">table relateion definition.</param>
        /// <param name="sortTableByConstraint">according the constraint relation.</param>
        /// <returns>An array of table names after sorting.</returns>
        public static string[] SortTables(Dictionary<string, DataRelationDefinition> dataRelationDefinitions, bool sortTableByConstraint)
        {
            Dictionary<string, DataRelationDefinition> dataRelationDefinitionsCopy = CopyRelationDefinitions(dataRelationDefinitions, sortTableByConstraint);
            List<string> tableNames = new List<string>();
            foreach (string relationName in dataRelationDefinitionsCopy.Keys)
            {
                string tableName = dataRelationDefinitionsCopy[relationName].ParentTable;
                if (!tableNames.Contains(tableName))
                {
                    tableNames.Add(tableName);
                }
            }

            List<string> sortedTableNames = new List<string>();
            // Use "Topological" method to sort the tables.
            TopologicalSort(ref tableNames, ref dataRelationDefinitionsCopy, ref sortedTableNames);

            string[] sortedTables = new string[sortedTableNames.Count];
            sortedTableNames.CopyTo(sortedTables);
            return sortedTables;
        }

        /// <summary>
        /// Use "Topological" method to sort the tables.
        /// </summary>
        /// <param name="tableNames">table names.</param>
        /// <param name="dataRelationDefinitions">table relateion definition.</param>
        /// <param name="sortedTableNames">sorted table names.</param>
        public static void TopologicalSort(ref List<string> tableNames, ref Dictionary<string, DataRelationDefinition> dataRelationDefinitions, ref List<string> sortedTableNames)
        {
            // 注意循环关联Relation 及自返Relation 。
            // definition 本次排序成功table name column table.
            List<string> removeTableNames = new List<string>();
            foreach (string tableName in tableNames)
            {
                // 用于check check if 最底层table.
                bool bottomTable = true;
                foreach (string relationName in dataRelationDefinitions.Keys)
                {
                    DataRelationDefinition dataRelationDefinition = dataRelationDefinitions[relationName];
                    if ((dataRelationDefinition.ParentTable == tableName)&&(dataRelationDefinition.ChildTable != tableName))
                    {
                        // 如果此table 为任何一个Relation 中parent ，并且Not 是自返Relation ，则此table 就Not 是最底层table.
                        bottomTable = false;
                        break;
                    }
                }
                // 如果table 位于为最底层，则排序成功。
                if (bottomTable)
                {
                    // Add 排序成功table.
                    sortedTableNames.Add(tableName);
                    removeTableNames.Add(tableName);
                    // 在table Relation 中移除与排序成功table 相关table Relation 。
                    RemoveDataRelationDefinition(ref dataRelationDefinitions, tableName);
                }
            }

            foreach (string tableName in removeTableNames)
            {
                // 将排序成功table 移除，Not 再参与排序。
                tableNames.Remove(tableName);
            }

            // 如果仍存在未排序成功table ，并且本次排序存在排序成功table ，则继续进行排序。
            if ((tableNames.Count > 0) && (removeTableNames.Count > 0))
            {
                TopologicalSort(ref tableNames, ref dataRelationDefinitions, ref sortedTableNames);
            }
            else
            {
                // 存在循环关联Relation 。
                throw new System.ArgumentException("在给定table Relation definition 定典中存在循环关联Relation ，Not 能进行排序。");
            }
        }

        /// <summary>
        /// remove the relation.
        /// </summary>
        /// <param name="dataRelationDefinitions">table relateion definition.</param>
        /// <param name="tableName">table name.</param>
        public static void RemoveDataRelationDefinition(ref Dictionary<string, DataRelationDefinition> dataRelationDefinitions, string tableName)
        {
            List<string> removeRelationNames = new List<string>();
            foreach (string relationName in dataRelationDefinitions.Keys)
            {
                DataRelationDefinition dataRelationDefinition = dataRelationDefinitions[relationName];
                if (dataRelationDefinition.ChildTable == tableName)
                {
                    removeRelationNames.Add(relationName);
                }
            }
            foreach (string relationName in removeRelationNames)
            {
                removeRelationNames.Add(relationName);
            }
        }
        #endregion
        #region Common property 
        #region
        private bool useColumnsForParameterNames = false;
        /// <summary>
        /// If the value is true, it will generate the parameter name as column name (if possible), if it's false, it will generate @p1, @p2 like that.
        /// </summary>
        public bool UseColumnsForParameterNames
        {
            get
            {
                return this.useColumnsForParameterNames;
            }
            set
            {
                this.useColumnsForParameterNames = value;
            }
        }

        private bool sortTableByConstraint = true;
        /// <summary>
        /// according the constraint relation.
        /// </summary>
        public bool SortTableByConstraint
        {
            get
            {
                return this.sortTableByConstraint;
            }
            set
            {
                this.sortTableByConstraint = value;
            }
        }
        #endregion
        protected System.Type dataSetType;
        /// <summary>
        /// dataset Type.
        /// </summary>
        public System.Type DataTableType
        {
            get
            {
                return this.dataSetType;
            }
        }

        private System.Xml.XmlElement xmlElement;
        /// <summary>
        /// xml element.
        /// </summary>
        public System.Xml.XmlElement XmlElement
        {
            get
            {
                return this.xmlElement;
            }
        }

        private Dictionary<string, DataTableDefinition> dataTableDefinitions;
        /// <summary>
        /// table struction definition dictionary.
        /// </summary>
        public Dictionary<string, DataTableDefinition> DataTableDefinitions
        {
            get
            {
                return this.dataTableDefinitions;
            }
        }

        private Dictionary<string, System.Data.DbType> dbTypes;

        public Dictionary<string, System.Data.DbType> DbTypes
        {
            get { return dbTypes; }
        }

        private Dictionary<string, DataRelationDefinition> dataRelationDefinitions;
        /// <summary>
        /// table relation definition dictionary.
        /// </summary>
        public Dictionary<string, DataRelationDefinition> DataRelationDefinitions
        {
            get
            {
                return this.dataRelationDefinitions;
            }
        }

        private string[] tableNames;
        /// <summary>
        /// dataset has table name.
        /// </summary>
        public string[] TableNames
        {
            get
            {
                if (this.tableNames == null)
                {
                    this.tableNames = GetSortedTableNamesInternal();
                }
                return this.tableNames;
            }
        }
        #endregion
        #region Constructor
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="type">Type of data object.</param>
        public DataSetCommonCommandBuilder(System.Type type)
        {
            this.dataSetType = type;
            this.xmlElement = DefinitionManager.Instance.LoadXmlDocument(type);
            this.dataTableDefinitions = GenerateDataTableDefinitions(this.dataSetType, this.xmlElement);
            this.dbTypes = GenerateDbTypes(this.dataTableDefinitions);
            this.dataRelationDefinitions = GenerateDataRelationDefinitions(this.xmlElement);
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="type">Type of data object.</param>
        /// <param name="name">Data object name.</param>
        public DataSetCommonCommandBuilder(System.Type type, string name)
        {
            this.dataSetType = type;
            this.xmlElement = DefinitionManager.Instance.LoadXmlDocument(type, name);
            this.dataTableDefinitions = GenerateDataTableDefinitions(this.dataSetType, this.xmlElement);
            this.dbTypes = GenerateDbTypes(this.dataTableDefinitions);
            this.dataRelationDefinitions = GenerateDataRelationDefinitions(this.xmlElement);
        }
        #endregion
        #region Common Methods
        /// <summary>
        /// Gets Common Command builder.
        /// </summary>
        /// <param name="name">table name.</param>
        /// <returns>Common Command builder.</returns>
        public CommonCommandBuilder GetCommonCommandBuilder(string name)
        {
            foreach (System.Xml.XmlNode tablesXmlNode in this.xmlElement)
            {
                if (tablesXmlNode is System.Xml.XmlElement)
                {
                    System.Xml.XmlElement tablesXmlElement = tablesXmlNode as System.Xml.XmlElement;
                    if (tablesXmlElement.Name == "Tables")
                    {
                        foreach (System.Xml.XmlNode tableXmlNode in tablesXmlElement)
                        {
                            if (tableXmlNode is System.Xml.XmlElement)
                            {
                                System.Xml.XmlElement tableXmlElement = tableXmlNode as System.Xml.XmlElement;
                                string tableName = tableXmlElement.Attributes["TableName"].Value.Trim();
                                if (tableName == name)
                                {
                                    if (tableXmlElement.Attributes["Type"] != null)
                                    {
                                        string typeName = tableXmlElement.Attributes["Type"].Value.Trim();
                                        // Gets datatable Type.
                                        System.Type dataTableType = GetDataTableType(dataSetType, typeName);

                                        if (tableXmlElement.Attributes["DefinitionName"] != null)
                                        {
                                            string definitionName = tableXmlElement.Attributes["DefinitionName"].Value.Trim();
                                            // Create Common Command builder.
                                            return CreateCommonCommandBuilder(dataTableType, definitionName);
                                        }
                                        else
                                        {
                                            // Create Common Command builder.
                                            return CreateCommonCommandBuilder(dataTableType);
                                        }
                                    }
                                    else
                                    {
                                        if (tableXmlElement.Attributes["DefinitionName"] != null)
                                        {
                                            string definitionName = tableXmlElement.Attributes["DefinitionName"].Value.Trim();
                                            // Create Common Command builder.
                                            return CreateCommonCommandBuilder(dataSetType, definitionName);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// Gets mapping ReplacedCommon Command.
        /// </summary>
        /// <param name="commonCommand">Common Command.</param>
        /// <returns>mapping ReplacedCommon Command.</returns>
        public virtual CommonCommand GetMappingCommand(CommonCommand commonCommand)
        {
            CommonCommand mappingCommand = new CommonCommand();
            string commandText = commonCommand.CommandText;
            foreach (DataTableDefinition dataTableDefinition in this.DataTableDefinitions.Values)
            {
                string tableName = string.Format(".[{0}]", dataTableDefinition.TableName);
                string sourceName = dataTableDefinition.SourceName;
                foreach (string columnName in dataTableDefinition.Columns.Keys)
                {
                    string columnColumnName = string.Format("{0}.[{1}]", tableName, columnName);
                    string columnSourceName = string.Format("{0}.{1}", sourceName, dataTableDefinition.Columns[columnName].SourceName);
                    commandText = commandText.Replace(columnColumnName, columnSourceName);
                }
                commandText = commandText.Replace(tableName, sourceName);
            }
            mappingCommand.CommandText = commandText;
            // addtion parameters.
            foreach (CommonParameter commonParameter in commonCommand.Parameters)
            {
                mappingCommand.Parameters.Add(commonParameter);
            }
            return mappingCommand;
        }

        /// <summary>
        /// Gets An array of table names after sorting.
        /// </summary>
        /// <returns>An array of table names after sorting.</returns>
        protected virtual string[] GetSortedTableNamesInternal()
        {
            return SortTables(this.dataRelationDefinitions, this.sortTableByConstraint);
        }

        /// <summary>
        /// Gets An array of table names after sorting.
        /// </summary>
        /// <returns>An array of table names after sorting.</returns>
        public virtual string[] GetSortedTableNames()
        {
            return this.TableNames;
        }

        /// <summary>
        /// Gets An array of table names after sorting.
        /// </summary>
        /// <param name="tableNames">An array of table names.</param>
        /// <returns>An array of table names after sorting.</returns>
        public virtual string[] GetSortedTableNames(string[] tableNames)
        {
            string[] sortedTableNames = new string[tableNames.Length];
            int i = 0;
            foreach (string tableName in this.TableNames)
            {
                if (ContainsValue(tableNames, tableName))
                {
                    sortedTableNames[i] = tableName;
                    i++;
                    if (i == sortedTableNames.Length)
                    {
                        break;
                    }
                }
            }
            return sortedTableNames;
        }


        /// <summary>
        /// Gets Relation command text.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <returns>Relation command text.</returns>
        public virtual string GetRelationCommandText(DataRelationDefinition dataRelationDefinition, int columnCount, DataTableDefinition parentTableDefinition, string parentTableName, DataTableDefinition childTableDefinition, string childTableName)
        {
            System.Text.StringBuilder relationCommandTextStringBuilder = new StringBuilder();
            int columnAppendCount = columnCount - 1;
            for (int i = 0; i < columnCount; i++)
            {
                string parentColumnName = dataRelationDefinition.ParentColumns[i];
                string parentColumnSourceName = parentTableDefinition.Columns[parentColumnName].SourceName;
                string childColumnName = dataRelationDefinition.ChildColumns[i];
                string childColumnSourceName = childTableDefinition.Columns[childColumnName].SourceName;

#if (PocketPC || Smartphone || WindowsCE)
                relationCommandTextStringBuilder.Append(string.Format("{0}.{1} = {2}.{3}", parentTableName, parentColumnSourceName, childTableName, childColumnSourceName));
#else
                relationCommandTextStringBuilder.AppendFormat("{0}.{1} = {2}.{3}", parentTableName, parentColumnSourceName, childTableName, childColumnSourceName);
#endif
                if (i < columnAppendCount)
                {
                    relationCommandTextStringBuilder.Append(" AND ");
                }
            }
            return relationCommandTextStringBuilder.ToString();
        }

        /// <summary>
        /// Gets Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="tableName">table name.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation Command.</returns>
        public virtual CommonCommand GetRelationCommand(DataRelationDefinition dataRelationDefinition, DataTableDefinition dataTableDefinition, CommonCommand conditionCommand, bool mappingCommand, DataTableDefinition parentTableDefinition, string parentTableName, DataTableDefinition childTableDefinition, string childTableName)
        {
            CommonCommand relationCommand = new CommonCommand();
            System.Text.StringBuilder relationCommandTextStringBuilder = new StringBuilder();

            string relationCommandText = GetRelationCommandText(dataRelationDefinition, dataRelationDefinition.ParentColumns.Length, parentTableDefinition, parentTableName, childTableDefinition, childTableName);
#if (PocketPC || Smartphone || WindowsCE)
            relationCommandTextStringBuilder.Append(string.Format("SELECT 1 FROM {0} WHERE ({1})", dataTableDefinition.SourceName, relationCommandText));
#else
            relationCommandTextStringBuilder.AppendFormat("SELECT 1 FROM {0} WHERE ({1})", dataTableDefinition.SourceName, relationCommandText);
#endif

            if ((conditionCommand != null) && (conditionCommand.CommandText != string.Empty))
            {
                if (mappingCommand)
                {
                    CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(dataTableDefinition.TableName);
                    CommonCommand conditionMappingCommand = commonCommandBuilder.GetMappingCommand(conditionCommand);
#if (PocketPC || Smartphone || WindowsCE)
                    relationCommandTextStringBuilder.Append(string.Format(" AND {0}", conditionMappingCommand.CommandText));
#else
                    relationCommandTextStringBuilder.AppendFormat(" AND {0}", conditionMappingCommand.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in conditionMappingCommand.Parameters)
                    {
                        relationCommand.Parameters.Add(commonParameter);
                    }
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    relationCommandTextStringBuilder.Append(string.Format(" AND {0}", conditionCommand.CommandText));
#else
                    relationCommandTextStringBuilder.AppendFormat(" AND {0}", conditionCommand.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in conditionCommand.Parameters)
                    {
                        relationCommand.Parameters.Add(commonParameter);
                    }
                }
            }
            relationCommand.CommandText = relationCommandTextStringBuilder.ToString();
            return relationCommand;
        }


        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(DataRelationDefinition dataRelationDefinition, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            DataTableDefinition parentTableDefinition = this.DataTableDefinitions[parentTableName];
            string parentTableSourceName = parentTableDefinition.SourceName;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            string parentTableSourceAlias = commonCommandBuilder.GetDataTableSourceAlias();

            string childTableName = dataRelationDefinition.ChildTable;
            DataTableDefinition childTableDefinition = this.DataTableDefinitions[childTableName];
            string childTableSourceName = childTableDefinition.SourceName;

            CommonCommand parentCommand = new CommonCommand();
            System.Text.StringBuilder parentCommandTextStringBuilder = new StringBuilder();
            CommonCommand relationCommand;
            if (parentTableSourceName == parentTableSourceAlias)
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, childTableDefinition, childCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceName);
            }
            else
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, childTableDefinition, childCondition, mappingCommand, parentTableDefinition, parentTableSourceAlias, childTableDefinition, childTableSourceName);
            }

            return relationCommand;
        }

        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(string relationName, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return this.GetParentRelationCommand(dataRelationDefinition, parentCondition, childCondition, mappingCommand);
        }
        
        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(string relationName, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentRelationCommand(dataRelationDefinition, primaryKeys);
        }

        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(DataRelationDefinition dataRelationDefinition, object[] primaryKeys)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder childCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping childDataTableMapping = childCommandBuilder.GetDataTableMapping();
            CommonCommand childCondition = childCommandBuilder.GetNormalConditionCommand(childDataTableMapping);

            return GetParentRelationCommand(dataRelationDefinition, null, childCondition, false);
        }
        
        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(string relationName, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentRelationCommand(dataRelationDefinition, conditionCommand);
        }

        /// <summary>
        /// Gets parent Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentRelationCommand(DataRelationDefinition dataRelationDefinition, CommonCommand conditionCommand)
        {
            CommonCommand childCondition = conditionCommand;

            return GetParentRelationCommand(dataRelationDefinition, null, childCondition, true);
        }
        

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentTableName">parent table name.</param>
        /// <param name="commonCommandBuilder">parent Command builder.</param>
        /// <param name="selectCommandText">query command text.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation parent query Command.</returns>
        protected virtual CommonCommand GetParentCommand(DataRelationDefinition dataRelationDefinition, string parentTableName, CommonCommandBuilder commonCommandBuilder, string selectCommandText, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataTableDefinition parentTableDefinition = this.DataTableDefinitions[parentTableName];
            string parentTableSourceName = parentTableDefinition.SourceName;
            string parentTableSourceAlias = commonCommandBuilder.GetDataTableSourceAlias();

            string childTableName = dataRelationDefinition.ChildTable;
            DataTableDefinition childTableDefinition = this.DataTableDefinitions[childTableName];
            string childTableSourceName = childTableDefinition.SourceName;

            CommonCommand parentCommand = new CommonCommand();
            System.Text.StringBuilder parentCommandTextStringBuilder = new StringBuilder();
            CommonCommand relationCommand;
            if (parentTableSourceName == parentTableSourceAlias)
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, childTableDefinition, childCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceName);
            }
            else
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, childTableDefinition, childCondition, mappingCommand, parentTableDefinition, parentTableSourceAlias, childTableDefinition, childTableSourceName);
            }
#if (PocketPC || Smartphone || WindowsCE)
            parentCommandTextStringBuilder.Append(string.Format("{0} WHERE EXISTS ({1})", selectCommandText, relationCommand.CommandText));
#else
            parentCommandTextStringBuilder.AppendFormat("{0} WHERE EXISTS ({1})", selectCommandText, relationCommand.CommandText);
#endif
            // addtion parameters.
            foreach (CommonParameter commonParameter in relationCommand.Parameters)
            {
                parentCommand.Parameters.Add(commonParameter);
            }
            if ((parentCondition != null) && (parentCondition.CommandText != string.Empty))
            {
                if (mappingCommand)
                {
                    CommonCommand conditionMappingCommand = commonCommandBuilder.GetMappingCommand(parentCondition);
#if (PocketPC || Smartphone || WindowsCE)
                    parentCommandTextStringBuilder.Append(string.Format(" AND {0}", conditionMappingCommand.CommandText));
#else
                    parentCommandTextStringBuilder.AppendFormat(" AND {0}", conditionMappingCommand.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in conditionMappingCommand.Parameters)
                    {
                        parentCommand.Parameters.Add(commonParameter);
                    }
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    parentCommandTextStringBuilder.Append(string.Format(" AND {0}", parentCondition.CommandText));
#else
                    parentCommandTextStringBuilder.AppendFormat(" AND {0}", parentCondition.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in parentCondition.Parameters)
                    {
                        parentCommand.Parameters.Add(commonParameter);
                    }
                }
            }
            parentCommand.CommandText = parentCommandTextStringBuilder.ToString();
            return parentCommand;
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentCommand(string relationName, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentCommand(dataRelationDefinition, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentCommand(string relationName, string[] columnNames, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentCommand(dataRelationDefinition, columnNames, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentCommand(DataRelationDefinition dataRelationDefinition, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, parentCondition, childCondition, mappingCommand);
        }


        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(string relationName, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentSelectCommand(dataRelationDefinition, primaryKeys);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(string relationName, string[] columnNames, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentSelectCommand(dataRelationDefinition, columnNames, primaryKeys);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(DataRelationDefinition dataRelationDefinition, object[] primaryKeys)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder childCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping childDataTableMapping = childCommandBuilder.GetDataTableMapping();
            CommonCommand childCondition = childCommandBuilder.GetNormalConditionCommand(childDataTableMapping);
  
            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, null, childCondition, false);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, object[] primaryKeys)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder childCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping childDataTableMapping = childCommandBuilder.GetDataTableMapping();
            CommonCommand childCondition = childCommandBuilder.GetNormalConditionCommand(childDataTableMapping);
 
            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, null, childCondition, false);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(string relationName, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentSelectCommand(dataRelationDefinition, conditionCommand);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(string relationName, string[] columnNames, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetParentSelectCommand(dataRelationDefinition, columnNames, conditionCommand);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(DataRelationDefinition dataRelationDefinition, CommonCommand conditionCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            CommonCommand childCondition = conditionCommand;
  
            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, null, childCondition, true);
        }

        /// <summary>
        /// Gets Relation parent query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation parent query Command.</returns>
        public virtual CommonCommand GetParentSelectCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, CommonCommand conditionCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            CommonCommand childCondition = conditionCommand;
 
            return GetParentCommand(dataRelationDefinition, parentTableName, commonCommandBuilder, selectCommandText, null, childCondition, true);
        }
        

        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(DataRelationDefinition dataRelationDefinition, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            DataTableDefinition childTableDefinition = this.DataTableDefinitions[childTableName];
            string childTableSourceName = childTableDefinition.SourceName;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            string childTableSourceAlias = commonCommandBuilder.GetDataTableSourceAlias();

            string parentTableName = dataRelationDefinition.ParentTable;
            DataTableDefinition parentTableDefinition = this.DataTableDefinitions[parentTableName];
            string parentTableSourceName = parentTableDefinition.SourceName;

            CommonCommand childCommand = new CommonCommand();
            System.Text.StringBuilder childCommandTextStringBuilder = new StringBuilder();
            CommonCommand relationCommand;
            if (childTableSourceName == childTableSourceAlias)
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, parentTableDefinition, parentCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceName);
            }
            else
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, parentTableDefinition, parentCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceAlias);
            }

            return relationCommand;
        }
        
        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(string relationName, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return this.GetChildRelationCommand(dataRelationDefinition, parentCondition, childCondition, mappingCommand);
        }
        
        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(string relationName, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildRelationCommand(dataRelationDefinition, primaryKeys);
        }

        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(DataRelationDefinition dataRelationDefinition, object[] primaryKeys)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder parentCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping parentDataTableMapping = parentCommandBuilder.GetDataTableMapping();
            CommonCommand parentCondition = parentCommandBuilder.GetNormalConditionCommand(parentDataTableMapping);

            return GetChildRelationCommand(dataRelationDefinition, parentCondition, null, false);
        }
        
        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(string relationName, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildRelationCommand(dataRelationDefinition, conditionCommand);
        }

        /// <summary>
        /// Gets child Relation Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildRelationCommand(DataRelationDefinition dataRelationDefinition, CommonCommand conditionCommand)
        {
            CommonCommand parentCondition = conditionCommand;

            return GetChildRelationCommand(dataRelationDefinition, parentCondition, null, true);
        }


        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="childTableName">child table name.</param>
        /// <param name="commonCommandBuilder">child Command builder.</param>
        /// <param name="selectCommandText">query command text.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child query Command.</returns>
        protected virtual CommonCommand GetChildCommand(DataRelationDefinition dataRelationDefinition, string childTableName, CommonCommandBuilder commonCommandBuilder, string selectCommandText, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string parentTableName = dataRelationDefinition.ParentTable;
            DataTableDefinition parentTableDefinition = this.DataTableDefinitions[parentTableName];
            string parentTableSourceName = parentTableDefinition.SourceName;

            DataTableDefinition childTableDefinition = this.DataTableDefinitions[childTableName];
            string childTableSourceName = childTableDefinition.SourceName;
            string childTableSourceAlias = commonCommandBuilder.GetDataTableSourceAlias();

            CommonCommand childCommand = new CommonCommand();
            System.Text.StringBuilder childCommandTextStringBuilder = new StringBuilder();
            CommonCommand relationCommand;
            if (childTableSourceName == childTableSourceAlias)
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, parentTableDefinition, parentCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceName);
            }
            else
            {
                relationCommand = GetRelationCommand(dataRelationDefinition, parentTableDefinition, parentCondition, mappingCommand, parentTableDefinition, parentTableSourceName, childTableDefinition, childTableSourceAlias);
            }
#if (PocketPC || Smartphone || WindowsCE)
            childCommandTextStringBuilder.Append(string.Format("{0} WHERE EXISTS ({1})", selectCommandText, relationCommand.CommandText));
#else
            childCommandTextStringBuilder.AppendFormat("{0} WHERE EXISTS ({1})", selectCommandText, relationCommand.CommandText);
#endif
            // addtion parameters.
            foreach (CommonParameter commonParameter in relationCommand.Parameters)
            {
                childCommand.Parameters.Add(commonParameter);
            }
            if ((childCondition != null) && (childCondition.CommandText != string.Empty))
            {
                if (mappingCommand)
                {
                    CommonCommand conditionMappingCommand = commonCommandBuilder.GetMappingCommand(childCondition);
#if (PocketPC || Smartphone || WindowsCE)
                    childCommandTextStringBuilder.Append(string.Format(" AND {0}", conditionMappingCommand.CommandText));
#else
                    childCommandTextStringBuilder.AppendFormat(" AND {0}", conditionMappingCommand.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in conditionMappingCommand.Parameters)
                    {
                        childCommand.Parameters.Add(commonParameter);
                    }
                }
                else
                {
#if (PocketPC || Smartphone || WindowsCE)
                    childCommandTextStringBuilder.Append(string.Format(" AND {0}", childCondition.CommandText));
#else
                    childCommandTextStringBuilder.AppendFormat(" AND {0}", childCondition.CommandText);
#endif
                    // addtion parameters.
                    foreach (CommonParameter commonParameter in childCondition.Parameters)
                    {
                        childCommand.Parameters.Add(commonParameter);
                    }
                }
            }
            childCommand.CommandText = childCommandTextStringBuilder.ToString();
            return childCommand;
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildCommand(string relationName, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildCommand(dataRelationDefinition, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildCommand(string relationName, string[] columnNames, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildCommand(dataRelationDefinition, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildCommand(DataRelationDefinition dataRelationDefinition, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, childCondition, mappingCommand);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, childCondition, mappingCommand);
        }


        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(string relationName, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildSelectCommand(dataRelationDefinition, primaryKeys);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(string relationName, string[] columnNames, object[] primaryKeys)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildSelectCommand(dataRelationDefinition, columnNames, primaryKeys);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(DataRelationDefinition dataRelationDefinition, object[] primaryKeys)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder parentCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping parentDataTableMapping = parentCommandBuilder.GetDataTableMapping();
            CommonCommand parentCondition = parentCommandBuilder.GetNormalConditionCommand(parentDataTableMapping);

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, null, false);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, object[] primaryKeys)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder parentCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping parentDataTableMapping = parentCommandBuilder.GetDataTableMapping();
            CommonCommand parentCondition = parentCommandBuilder.GetNormalConditionCommand(parentDataTableMapping);

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, null, false);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(string relationName, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildSelectCommand(dataRelationDefinition, conditionCommand);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="relationName">relation name.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(string relationName, string[] columnNames, CommonCommand conditionCommand)
        {
            DataRelationDefinition dataRelationDefinition = this.DataRelationDefinitions[relationName];
            return GetChildSelectCommand(dataRelationDefinition, columnNames, conditionCommand);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(DataRelationDefinition dataRelationDefinition, CommonCommand conditionCommand)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(out dataTableMapping);

            CommonCommand parentCondition = conditionCommand;

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, null, true);
        }

        /// <summary>
        /// Gets Relation child query Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="columnNames">An array of Columns.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child query Command.</returns>
        public virtual CommonCommand GetChildSelectCommand(DataRelationDefinition dataRelationDefinition, string[] columnNames, CommonCommand conditionCommand)
        {
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            System.Data.Common.DataTableMapping dataTableMapping;
            string selectCommandText = commonCommandBuilder.BuildSelectCommandText(columnNames, out dataTableMapping);

            CommonCommand parentCondition = conditionCommand;

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, selectCommandText, parentCondition, null, true);
        }


        /// <summary>
        /// Gets Relation child Delete Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="primaryKeys">The value of primary key.</param>
        /// <returns>Relation child Delete Command.</returns>
        public virtual CommonCommand GetChildDeleteCommand(DataRelationDefinition dataRelationDefinition, object[] primaryKeys)
        {
            // Gets Relation child Delete Command.
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            string deleteCommandText = commonCommandBuilder.BuildDeleteCommandText();

            string parentTableName = dataRelationDefinition.ParentTable;
            CommonCommandBuilder parentCommandBuilder = GetCommonCommandBuilder(parentTableName);
            System.Data.Common.DataTableMapping parentDataTableMapping = parentCommandBuilder.GetDataTableMapping();
            CommonCommand parentCondition = parentCommandBuilder.GetNormalConditionCommand(parentDataTableMapping);

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, deleteCommandText, parentCondition, null, false);
        }

        /// <summary>
        /// Gets Relation child Delete Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="conditionCommand">Condition Command.</param>
        /// <returns>Relation child Delete Command.</returns>
        public virtual CommonCommand GetChildDeleteCommand(DataRelationDefinition dataRelationDefinition, CommonCommand conditionCommand)
        {
            // Gets Relation child Delete Command.
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            string deleteCommandText = commonCommandBuilder.BuildDeleteCommandText();

            CommonCommand parentCondition = conditionCommand;

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, deleteCommandText, parentCondition, null, true);
        }

        /// <summary>
        /// Gets Relation child Delete Command.
        /// </summary>
        /// <param name="dataRelationDefinition">Definition of relation.</param>
        /// <param name="parentCondition">parent Condition 。</param>
        /// <param name="childCondition">child Condition 。</param>
        /// <returns>Relation child Delete Command.</returns>
        public virtual CommonCommand GetDeleteChildCommand(DataRelationDefinition dataRelationDefinition, CommonCommand parentCondition, CommonCommand childCondition, bool mappingCommand)
        {
            // Gets Relation child Delete Command.
            string childTableName = dataRelationDefinition.ChildTable;
            CommonCommandBuilder commonCommandBuilder = GetCommonCommandBuilder(childTableName);
            string deleteCommandText = commonCommandBuilder.BuildDeleteCommandText();

            return GetChildCommand(dataRelationDefinition, childTableName, commonCommandBuilder, deleteCommandText, parentCondition, childCondition, mappingCommand);
        }
        #endregion

        private Dictionary<string, System.Data.DbType> GenerateDbTypes(Dictionary<string, DataTableDefinition> dataTableDefinitions)
        {
            Dictionary<string, System.Data.DbType> dbTypes = new Dictionary<string, System.Data.DbType>();
            foreach (KeyValuePair<string, DataTableDefinition> dataTable in dataTableDefinitions)
            {
                foreach (KeyValuePair<string, DataColumnDefinition> column in dataTable.Value.Columns)
                {
                    string columnName = string.Format("{0}.{1}", dataTable.Key, column.Key);
                    dbTypes.Add(columnName, column.Value.DbType);
                }
            }
            return dbTypes;
        }
    }
}
